package com.test.poitest.controller;

import com.test.poitest.utils.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author: 80003837
 * @create: 2019-06-04 16:40
 **/
@Controller
public class PoiOperation {

    @PostMapping("upload")
    public String upload(@RequestParam("file") MultipartFile file) {
        //获取表格
        Workbook book = null;

        try {
            //判断是xls还是xlsx
            book = new XSSFWorkbook(file.getInputStream());
        } catch (Exception e) {
            try {
                book = new HSSFWorkbook(file.getInputStream());
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            //校验表格内的内容是否正确
            String result = checkout(book);
            if (!StringUtils.isEmpty(result)) {
                return result;
            }

        }

//List<Demo> demoList = new ArrayList<Demo>();
//
//
//
// 
//
////获取一共有多少sheet,遍历
//int numberOfSheets = book.getNumberOfSheets();
//for (int i = 0; i < numberOfSheets; i++) {
//Sheet sheet = book.getSheetAt(i);
////获取sheet中有多少行，遍历每一行
//int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
//for (int j = 0; j < physicalNumberOfRows; j++) {
//if (j == 0) {
//continue;//标题行
//}
//       Demo demo = new Demo();
//Row row = sheet.getRow(j);//获得当前行数据
//demo.setName(row.getCell(0).getStringCellValue()); //姓名
//String sex = row.getCell(1).getStringCellValue();
//if ("男".equals(sex)) {
//demo.setSex(1); //性别
//} else if ("女".equals(sex)) {
//demo.setSex(0); //性别
//}
//demo.setStudyYear(( long) row.getCell(2).getNumericCellValue());
//demoList.add(demo);
//}
//}
//int num = bmStudentService.addStudentBatch(studentList);
//if (num > 0) {
//r.setResult(num);
//r.setStatus("ok");
//} else {
//r.setResult(0);
//r.setStatus("error");
//}
//}
//
//
//    } catch(
//    Exception e)
//
//    {
//r.setResult(e.getClass().getName() + ":" + e.getMessage());
//r.setStatus("error");
//e.printStackTrace();
//}
//return JSON.toJSONString(r);


        return "success";
    }

    /**
     * 下载excel文件
     *
     * @param request
     * @param response
     */
    @RequestMapping("download")
    public void download(HttpServletRequest request, HttpServletResponse response) {
        try {
            exportExcel(request, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成一个excel文件,传输给浏览器
     *
     * @param request
     * @param response
     * @throws IOException
     */
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {

        //1.在内存中创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet();
        //3.创建标题行
        HSSFRow titlerRow = sheet.createRow(0);
        titlerRow.createCell(0).setCellValue("格口编号");
        titlerRow.createCell(1).setCellValue("格口名称");


        //5.创建文件名
        String fileName = "格口自定义名称.xls";
        //6.获取输出流对象
        ServletOutputStream outputStream = response.getOutputStream();

        //7.获取mimeType
        ServletContext servletContext = request.getServletContext();
        String mimeType = servletContext.getMimeType(fileName);
        //8.获取浏览器信息,对文件名进行重新编码
        fileName = FileUtils.filenameEncoding(fileName, request);

        //9.设置信息头
        response.setContentType(mimeType);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        //10.写出文件,关闭流
        hssfWorkbook.write(outputStream);
        hssfWorkbook.close();
    }

    /**
     * 校验excel表格内的内容是否正确
     */
    private String checkout(Workbook book) {
        //获取一共有多少sheet(页),遍历
        int numberOfSheets = book.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            //获取当前页
            Sheet sheetAt = book.getSheetAt(i);
            //获取sheet中有多少行，遍历每一行
            int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    continue;//标题行
                }
                //获得当前行数据
                Row row = sheetAt.getRow(j);
                try {
                    String boxNo = row.getCell(0).getStringCellValue();
                    //第一个值因为要转换成int类型,所以这里如果转换不了则直接报错
                    Integer boxNoInt = Integer.parseInt(boxNo.trim());

                } catch (Exception e) {
                    e.printStackTrace();
                    return "第" + i + "页,第" + (j + 1) + "行编号输入值错误";
                }
                String boxName = row.getCell(1).getStringCellValue();
                if (StringUtils.isEmpty(boxName)||"".equals(boxName.trim())){
                    return "第" + i + "页,第" + (j + 2) + "行编号输入值错误";
                }
            }
        }
        return null;
    }
}
